﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Data;
using System.Data.Common;
using System.Drawing;
using System.IO;
using System.Reflection;

namespace Faust.Andrew.MTG
{
    /// <summary>
    /// Manages Data Storage and Retrieval for Magic Manager. All data is stored in SQL Lite.
    /// </summary>
    public class DataStore
    {
        private readonly int _currentVersion = 3;
        private bool _isCurrentDb;

        //How many cards to return on a page.
        private const int _pageSize = 50;

        private string _dataFilePath;
        protected string DataFilePath
        {
            get
            {
                return _dataFilePath;
            }

            set
            {
            	_dataFilePath = value;
                _connectionString = String.Format("Data Source={0}; Version=3;", value);
            }
        }

        //protected string ImagePath { get; set; }
        
        private string _connectionString;
        public string ConnectionString
        {
            get
            {
                return _connectionString;
            }
        }

        public bool IsCurrentVersion
        {
            get
            {
                return _isCurrentDb;
            }
        }

        /// <summary>
        /// Path to the SQL Lite Data File the class should work with
        /// </summary>
        /// <param name="dataFile"></param>
        public DataStore(string dataDirectory)
        {
            InitializeDataFile(dataDirectory);
        }

        private void InitializeDataFile(string dataDirectory)
        {
            //string impagePath = dataDirectory + "\\Images";

            string dataFile = dataDirectory + "\\MagicManager.db";

            if (!File.Exists(dataFile))
            {
                if (!Directory.Exists(dataDirectory))
                {
                    Directory.CreateDirectory(dataDirectory);
                }

                Assembly currentAssembly = Assembly.GetExecutingAssembly();
                using (Stream dbStream = currentAssembly.GetManifestResourceStream("Faust.Andrew.MTG.MagicManager.db"))
                {
                    using (FileStream fileStream = new FileStream(dataFile, FileMode.Create, FileAccess.Write, FileShare.None))
                    {
                        fileStream.Seek(0, SeekOrigin.Begin);
                        dbStream.Seek(0, SeekOrigin.Begin);
                        int bufferSize = 4096;
                        byte[] buffer = new byte[bufferSize];
                        int bytesRead = 0;

                        while ((bytesRead = dbStream.Read(buffer, 0, bufferSize)) > 0)
                        {
                            fileStream.Write(buffer, 0, bytesRead);
                        }
                    }
                }
            }

            DataFilePath = dataFile;
           
            CheckDbVersion();
        }

        //Gets the database update script for the supplied version.
        private string GetUpdateScript(int version)
        {
            switch (version)
            {
                case 1:
                    return Scripts.V1To2;
                case 2:
                    return Scripts.V2To3;
                default:
                    throw new IndexOutOfRangeException(String.Format("Database version {0} does not have an associated update script", version));
            }
        }

        private void CheckDbVersion()
        {
            int dbVersion = GetDbVersion();

            //If the db is already the latest version we flag it correct and return.
            if (dbVersion == _currentVersion)
            {
                _isCurrentDb = true;
                return;
            }

            //if the database is version <= 0 then it's prior to when I started tracking
            //database version. The upgrade has to be handled manually by the user.
            if (dbVersion <= 0)
            {
                _isCurrentDb = false;
                return;
            }

            //It's a version after I started building automatic update scripts. Need to update the database.
            UpdateDatabase(dbVersion);
        }

        private void UpdateDatabase(int dbVersion)
        {
            using (SQLiteConnection connection = new SQLiteConnection(ConnectionString))
            {
                connection.Open();

                using (SQLiteTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        //Loop through all the versions between the current db version and the required
                        //db version. Run all the VXToY scripts in order.
                        for (int i = dbVersion; i < _currentVersion; i++)
                        {
                            using (SQLiteCommand command = new SQLiteCommand())
                            {
                                command.Connection = connection;
                                command.CommandText = GetUpdateScript(i);

                                command.ExecuteNonQuery();
                            }
                        }

                        transaction.Commit();
                        _isCurrentDb = true;
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }

        public void BackupRepository(string backupDirectory)
        {
            DirectoryInfo dir = new DirectoryInfo(backupDirectory);            
                                      
            ExportCards(backupDirectory + "\\Cards.txt");

            foreach (string deckName in ListDecks())
            {
                ExportDeck(deckName, backupDirectory + "\\" + CleanDeckName(deckName) + ".txt");
            }
        }

        private string CleanDeckName(string deckName)
        {
            string newDeckName = deckName.Replace("\\", "_");
            newDeckName = newDeckName.Replace("&", "_");
            newDeckName = newDeckName.Replace("/", "_");
            newDeckName = newDeckName.Replace("+", "_");
            newDeckName = newDeckName.Replace("-", "_");

            return newDeckName;
        }

        private int GetDbVersion()
        {
            try
            {
                using (SQLiteConnection connection = new SQLiteConnection(ConnectionString))
                {
                    connection.Open();

                    using (SQLiteCommand command = new SQLiteCommand())
                    {
                        command.Connection = connection;
                        command.CommandText = "select VersionID from Version where ID=0";
                        int versionId = (int)command.ExecuteScalar();

                        return versionId;
                    }
                }
            }
            catch
            {
                return 0;
            }
        }

        public void ExportCards(string fileName)
        {
            using (SQLiteConnection connection = new SQLiteConnection(ConnectionString))
            {
                connection.Open();
                using (StreamWriter streamWriter = new StreamWriter(fileName, false, System.Text.Encoding.ASCII))
                {
                    TextWriter writer = streamWriter;

                    using (SQLiteCommand command = new SQLiteCommand())
                    {
                        command.Connection = connection;
                        command.CommandText = "select [Name], [Quantity], [MultiverseID] from Cards where [Quantity] > 0";

                        using (SQLiteDataReader reader = command.ExecuteReader())
                        {
                            writer.WriteLine("Name\tQuantity\tMultiverseID");

                            while (reader.Read())
                            {
                                writer.WriteLine("{0}\t{1}\t{2}", reader["Name"], reader["Quantity"], reader["MultiverseID"]);
                            }
                        }
                    }
                }
            }
        }

        public void ExportDeck(string deckName, string fileName)
        {
            using (SQLiteConnection connection = new SQLiteConnection(ConnectionString))
            {
                connection.Open();
                using (StreamWriter streamWriter = new StreamWriter(fileName, false, System.Text.Encoding.ASCII))
                {
                    TextWriter writer = streamWriter;

                    using (SQLiteCommand command = new SQLiteCommand())
                    {
                        command.Connection = connection;
                        command.CommandText = "select c.Name, dc.Quantity, c.MultiverseID from Cards c join DeckCards dc on c.MultiverseID=dc.MultiverseID join Decks d on d.DeckId=dc.DeckId where d.Name=@DeckName";
                        command.Parameters.AddWithValue("@DeckName", deckName);

                        using (SQLiteDataReader reader = command.ExecuteReader())
                        {
                            writer.WriteLine("Name\tQuantity\tMultiverseID");

                            while (reader.Read())
                            {
                                writer.WriteLine("{0}\t{1}\t{2}", reader["Name"], reader["Quantity"], reader["MultiverseID"]);
                            }
                        }
                    }
                }
            }
        }

        #region Cards
        public Card FindCard(string multiverseID)
        {
            using (SQLiteConnection connection = new SQLiteConnection(ConnectionString))
            {
                connection.Open();
                using (SQLiteCommand command = new SQLiteCommand())
                {
                    command.Connection = connection;

                    command.CommandText = "select * from View_Cards where [MultiverseID]=@MultiverseID";
                    command.Parameters.AddWithValue("@MultiverseID", multiverseID);

                    using (SQLiteDataReader reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            return LoadCard(reader);
                        }
                    }
                }
            }

            return null;
        }

        private CardSearchResults FindCardsById(string multiverseId)
        {
            CardSearchResults results = new CardSearchResults();
            results.Page = 0;
            results.Pages = 0;
            results.TotalCards = 0;

            Card card = FindCard(multiverseId);

            if (card != null)
            {
                results.Cards.Add(card);
                results.TotalCards = 1;
            }

            return results;
        }

        public CardSearchResults FindCards(CardSearchParameters searchParameters)
        {
            //If the ID is passed in we can bypass all other checks
            if (!String.IsNullOrEmpty(searchParameters.MultiverseID))
            {
                return FindCardsById(searchParameters.MultiverseID);
            }

            CardSearchResults results = new CardSearchResults();

            using (SQLiteConnection connection = new SQLiteConnection(ConnectionString))
            {
                connection.Open();
                using (SQLiteCommand command = new SQLiteCommand())
                {
                    command.Connection = connection;
                    int parameterCount = 0;

                    command.CommandText = "select * from View_Cards";

                    string whereClause = "";

                    if (!String.IsNullOrEmpty(searchParameters.CustomWhereClause))
                    {
                        whereClause = searchParameters.CustomWhereClause;
                        parameterCount = 1;
                    }
                    else
                    {
                        ConstructFindCardsWhereClause(searchParameters, command, ref parameterCount, ref whereClause);
                    }

                    if (parameterCount > 0)
                    {
                        command.CommandText += " where " + whereClause;
                    }

                    command.CommandText += GetOrderByClause(searchParameters.SortOrder);

                    using (SQLiteDataReader reader = command.ExecuteReader())
                    {
                        int quantity = 0;
                        int currentCount = 0;

                        int page = searchParameters.Page;
                        if (page < 0) { page = 0; }

                        int startingCount = page * _pageSize;

                        while (reader.Read())
                        {
                            //We need the total count for the paging controls so we will loop through the entire returned set.
                            //However, we only want to include the cards that are in the output "Page". Starting at the beginning of a given
                            //page, we take a max of _pageSize cards.
                            if (currentCount >= startingCount && quantity < _pageSize)
                            {
                                results.Cards.Add(LoadCard(reader));
                                quantity++;
                            }

                            currentCount++;                            
                        }

                        results.Page = page;
                        results.TotalCards = currentCount;
                        results.Pages = currentCount / _pageSize;
                    }
                }
            }

            return results;
        }

        private static void ConstructFindCardsWhereClause(CardSearchParameters searchParameters, SQLiteCommand command, ref int parameterCount, ref string whereClause)
        {
            if (!String.IsNullOrEmpty(searchParameters.Name))
            {
                parameterCount++;
                if (parameterCount > 1)
                {
                    whereClause += " and ";
                }

                whereClause += "[Name] like @Name";
                command.Parameters.AddWithValue("@Name", String.Format("%{0}%", searchParameters.Name));
            }

            if (!String.IsNullOrEmpty(searchParameters.Color))
            {
                parameterCount++;
                if (parameterCount > 1)
                {
                    whereClause += " and ";
                }

                whereClause += "(([Color] like @Color) or ([ManaCost] like @Color))";
                command.Parameters.AddWithValue("@Color", String.Format("%{0}%", searchParameters.Color));
            }

            if (!String.IsNullOrEmpty(searchParameters.Rarity))
            {
                parameterCount++;
                if (parameterCount > 1)
                {
                    whereClause += " and ";
                }

                whereClause += "[Rarity] = @Rarity";
                command.Parameters.AddWithValue("@Rarity", String.Format("{0}", searchParameters.Rarity));
            }

            if (!String.IsNullOrEmpty(searchParameters.Type))
            {
                parameterCount++;
                if (parameterCount > 1)
                {
                    whereClause += " and ";
                }

                whereClause += "[CardTypes] like @Type";
                command.Parameters.AddWithValue("@Type", String.Format("%{0}%", searchParameters.Type));
            }

            if (!String.IsNullOrEmpty(searchParameters.SubType))
            {
                parameterCount++;
                if (parameterCount > 1)
                {
                    whereClause += " and ";
                }

                whereClause += "[CardTypes] like @SubType";
                command.Parameters.AddWithValue("@SubType", String.Format("%{0}%", searchParameters.SubType));
            }

            if (!String.IsNullOrEmpty(searchParameters.Text))
            {
                parameterCount++;
                if (parameterCount > 1)
                {
                    whereClause += " and ";
                }

                whereClause += "[CardText] like @Text";
                command.Parameters.AddWithValue("@Text", String.Format("%{0}%", searchParameters.Text));
            }

            if (!String.IsNullOrEmpty(searchParameters.Expansion))
            {
                parameterCount++;
                if (parameterCount > 1)
                {
                    whereClause += " and ";
                }

                whereClause += "[Expansion] = @Expansion";
                command.Parameters.AddWithValue("@Expansion", searchParameters.Expansion);
            }

            if (searchParameters.Owned)
            {
                parameterCount++;
                if (parameterCount > 1)
                {
                    whereClause += " and ";
                }

                whereClause += "[Quantity] > 0";
            }
        }

        private string GetOrderByClause(SortOrder sortOrder)
        {
            switch (sortOrder)
            {
                case SortOrder.Color:
                    return " Order by Color, Name";
                case SortOrder.Cost:
                    return " Order by ConvertedManaCostNumeric, Name";
                case SortOrder.Name:
                    return " Order by Name";
                case SortOrder.Rarity:
                    return " Order by Rarity, Name";
                default:
                    return " Order by Rarity, Name";
            }
        }

        public void AddCards(List<Card> cards, bool addToExistingQuantity)
        {
            using (SQLiteConnection connection = new SQLiteConnection(ConnectionString))
            {
                connection.Open();
                using (SQLiteTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        cards.ForEach(card => AddCard(card, addToExistingQuantity, connection, transaction));
                        transaction.Commit();
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }

        public void AddCard(Card card, bool addToExistingQuantity)
        {
            List<Card> cards = new List<Card>();
            cards.Add(card);
            AddCards(cards, addToExistingQuantity);
        }

        public void UpdateCardQuantity(string multiverseID, int quantity, bool addToExistingQuantity)
        {
            using (SQLiteConnection connection = new SQLiteConnection(ConnectionString))
            {
                connection.Open();

                using (SQLiteTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        UpdateCardQuantity(multiverseID, quantity, addToExistingQuantity, connection, transaction);
                        transaction.Commit();
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }

        private void UpdateCardQuantity(string multiverseID, int quantity, bool addToExistingQuantity, SQLiteConnection connection, SQLiteTransaction transaction)
        {
            using (SQLiteCommand command = new SQLiteCommand(connection))
            {
                command.Transaction = transaction;

                if (addToExistingQuantity)
                {
                    command.CommandText = "update [Cards] set [Quantity] = [Quantity] + @Quantity where [MultiverseID] = @MultiverseID";
                }
                else
                {
                    command.CommandText = "update [Cards] set [Quantity] = @Quantity where [MultiverseID] = @MultiverseID";
                }
                command.Parameters.AddWithValue("@Quantity", quantity);
                command.Parameters.AddWithValue("@MultiverseID", multiverseID);

                command.ExecuteNonQuery();
            }
        }

        private bool IsCardInStore(string multiverseID, SQLiteConnection connection, SQLiteTransaction transaction)
        {
            using (SQLiteCommand command = new SQLiteCommand(connection))
            {
                command.Transaction = transaction;
                command.CommandText = "select 1 from [Cards] where [MultiverseID]=@MultiverseID";
                command.Parameters.AddWithValue("@MultiverseID", multiverseID);

                object result = command.ExecuteScalar();

                //If the result isn't populated it wasn't found
                return (result != null && result != DBNull.Value);
            }
        }

        private void AddCard(Card card, bool addToExistingQuantity, SQLiteConnection connection, SQLiteTransaction transaction)
        {
            //If this card has already been stored we update the quantity owned and move on.
            if (IsCardInStore(card.MultiverseID, connection, transaction))
            {
                UpdateCardQuantity(card.MultiverseID, card.Quantity, addToExistingQuantity, connection, transaction);
                return;
            }

            using (SQLiteCommand command = new SQLiteCommand(connection))
            {
                command.Transaction = transaction;

                command.CommandText = "insert into [Cards] ([MultiverseID], [Rarity], [Toughness], "
                    + "[ToughnessNumeric], [Power], [PowerNumeric], [CardTypes], [ConvertedManaCost], "
                    + "[ConvertedManaCostNumeric], [Name], [CardText], [ManaCost], [Quantity], [Color], [Expansion], [SubType], [Image]) values ("
                    + "@MultiverseID, @Rarity, @Toughness, @ToughnessNumeric, @Power, @PowerNumeric, "
                    + "@CardTypes, @ConvertedManaCost, @ConvertedManaCostNumeric, @Name, @CardText, "
                    + "@ManaCost, @Quantity, @Color, @Expansion, @SubType, @Image)";

                command.Parameters.AddWithValue("@MultiverseID", card.MultiverseID);
                command.Parameters.AddWithValue("@Rarity", card.Rarity);
                command.Parameters.AddWithValue("@Toughness", card.Toughness);
                command.Parameters.AddWithValue("@ToughnessNumeric", card.ToughnessNumeric);
                command.Parameters.AddWithValue("@Power", card.Power);
                command.Parameters.AddWithValue("@PowerNumeric", card.PowerNumeric);
                command.Parameters.AddWithValue("@CardTypes", card.CardTypes);
                command.Parameters.AddWithValue("@ConvertedManaCost", card.ConvertedManaCost);
                command.Parameters.AddWithValue("@ConvertedManaCostNumeric", card.ConvertedManaCostNumeric);
                command.Parameters.AddWithValue("@Name", card.Name);
                command.Parameters.AddWithValue("@CardText", card.CardText);
                command.Parameters.AddWithValue("@ManaCost", card.ManaCost);
                command.Parameters.AddWithValue("@Quantity", card.Quantity);
                command.Parameters.AddWithValue("@Color", card.Color);
                command.Parameters.AddWithValue("@Expansion", card.Expansion);
                command.Parameters.AddWithValue("@SubType", card.SubType);
                command.Parameters.AddWithValue("@Image", BytesFromStream(card.GetImageStream()));

                command.ExecuteNonQuery();

                //SaveCardImage(card.MultiverseID, card.CardImage);
            }
        }

        private byte[] BytesFromStream(Stream stream)
        {
            stream.Seek(0, SeekOrigin.Begin);
            byte[] data = new byte[stream.Length];
            stream.Read(data, 0, data.Length);
            return data;
        }

        /// <summary>
        /// Creates a new Card object for the data the passed in reader is currently set to.
        /// </summary>
        /// <remarks>The reader MUST already be pointing at a record from the cards table</remarks>
        /// <param name="reader"></param>
        /// <returns></returns>
        private Card LoadCard(SQLiteDataReader reader)
        {
            Card card = new Card();

            card.MultiverseID = ReadString(reader, "MultiverseID");
            card.Rarity = ReadString(reader, "Rarity");
            card.Toughness = ReadString(reader, "Toughness");
            card.Power = ReadString(reader, "Power");
            card.CardTypes = ReadString(reader, "CardTypes");
            card.ConvertedManaCost = ReadString(reader, "ConvertedManaCost");
            card.Name = ReadString(reader, "Name");
            card.CardText = ReadString(reader, "CardText");
            card.ManaCost = ReadString(reader, "ManaCost");
            card.ToughnessNumeric = ReadInt(reader, "ToughnessNumeric");
            card.PowerNumeric = ReadInt(reader, "PowerNumeric");
            card.ConvertedManaCostNumeric = ReadInt(reader, "ConvertedManaCostNumeric");
            card.Quantity = ReadInt(reader, "Quantity");
            card.InUse = ReadInt(reader, "InUse");
            card.Expansion = ReadString(reader, "Expansion");
            card.SubType = ReadString(reader, "SubType");
            //card.SetImage(GetImagePath(card.MultiverseID));
            card.SetImage(new MemoryStream((byte[])reader["Image"]));

            return card;
        }        
        #endregion

        #region Decks
        public Deck CreateDeck(string name) { return CreateDeck(name, String.Empty); }

        public Deck CreateDeck(string name, string description)
        {
            using (SQLiteConnection connection = new SQLiteConnection(ConnectionString))
            {
                connection.Open();
                return CreateDeck(name, description, connection, null);   
            }
        }

        private Deck CreateDeck(string name, 
            string description, 
            SQLiteConnection connection, 
            SQLiteTransaction transaction)
        {
            using (SQLiteCommand command = new SQLiteCommand(connection))
            {
                command.Transaction = transaction;
                
                command.CommandText = "insert into [Decks] ([Name], [Description]) values (@Name, @Description); ";
                command.CommandText += "select last_insert_rowid() as RecordID;";

                command.Parameters.AddWithValue("@Name", name);
                command.Parameters.AddWithValue("@Description", description);

                Deck deck = new Deck();
                deck.DeckID = Convert.ToInt32(command.ExecuteScalar());                
                deck.Name = name;
                deck.Description = description;

                return deck;
            }
        }
                
        public void UpdateDeck(Deck deck) { UpdateDeck(deck, false); }

        public void UpdateDeck(Deck deck, bool updateCards)
        {
            using (SQLiteConnection connection = new SQLiteConnection(ConnectionString))
            {
                connection.Open();

                using (SQLiteTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        UpdateDeckHeader(deck, connection, transaction);

                        if (updateCards)
                        {
                            UpdateDeckCards(deck, connection, transaction);
                        }

                        transaction.Commit();
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }

        private void UpdateDeckCards(Deck deck, SQLiteConnection connection, SQLiteTransaction transaction)
        {
            //For simplicity we simply delete all the deck to card links and recreate for the existing cards.
            DeleteDeckCards(deck.DeckID, connection, transaction);

            foreach (DeckCard card in deck.Cards)
            {
                if (card.Quantity > 0)
                {
                    AddCardToDeck(deck.DeckID, card.Card.MultiverseID, card.Quantity, connection, transaction);
                }
            }
        }

        private void AddCardToDeck(int deckID, 
            string multiverseID, 
            int quantity, 
            SQLiteConnection connection, 
            SQLiteTransaction transaction)
        {
            using (SQLiteCommand command = new SQLiteCommand(connection))
            {
                command.Transaction = transaction;
                command.CommandText = "INSERT into [DeckCards] ([DeckID], [MultiverseID], [Quantity]) values (@DeckID, @MultiverseID, @Quantity)";
                command.Parameters.AddWithValue("@DeckID", deckID);
                command.Parameters.AddWithValue("@MultiverseID", multiverseID);
                command.Parameters.AddWithValue("@Quantity", quantity);

                try
                {
                    command.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    throw new Exception("Error adding card " + multiverseID, e);
                }
            }
        }

        private void UpdateDeckHeader(Deck deck, SQLiteConnection connection, SQLiteTransaction transaction)
        {
            using (SQLiteCommand command = new SQLiteCommand(connection))
            {
                command.Transaction = transaction;
                command.CommandText = "UPDATE [Decks] set [Name]=@Name, [Description]=@Description, [IsConstructed]=@IsConstructed where DeckID=@DeckID";
                command.Parameters.AddWithValue("@Name", deck.Name);
                command.Parameters.AddWithValue("@Description", deck.Description);
                command.Parameters.AddWithValue("@DeckID", deck.DeckID);
                command.Parameters.AddWithValue("@IsConstructed", deck.IsConstructed);

                command.ExecuteNonQuery();
            }
        }
        
        private void DeleteDeckCards(int deckID, SQLiteConnection connection, SQLiteTransaction transaction)
        {
            //Delete all the cards associated with a deck.

            using (SQLiteCommand command = new SQLiteCommand(connection))
            {
                command.Transaction = transaction;
                command.CommandText = "DELETE from [DeckCards] where [DeckID]=@DeckID";
                command.Parameters.AddWithValue("@DeckID", deckID);
                command.ExecuteNonQuery();
            }
        }

        public void DeleteDeck(int deckID)
        {
            using (SQLiteConnection connection = new SQLiteConnection(ConnectionString))
            {
                connection.Open();

                using (SQLiteTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        using (SQLiteCommand command = new SQLiteCommand(connection))
                        {
                            //First need to delete all the links between the deck and the cards in the deck.
                            DeleteDeckCards(deckID, connection, transaction);


                            //Now we can delete the deck
                            command.Transaction = transaction;                            
                            command.CommandText = "DELETE from [Decks] where [DeckID]=@DeckID";
                            command.Parameters.AddWithValue("@DeckID", deckID);
                            command.ExecuteNonQuery();

                            transaction.Commit();
                        }
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }

        public void DeleteDeck(Deck deck)
        {
            DeleteDeck(deck.DeckID);
        }
        
        public Deck GetDeck(int deckID)
        {
            using (SQLiteCommand command = new SQLiteCommand())
            {
                command.CommandText = "select * from [Decks] where [DeckID]=@DeckID";
                command.Parameters.AddWithValue("@DeckID", deckID);

                return GetDeck(command);
            }
        }

        public Deck GetDeck(string name)
        {
            using (SQLiteCommand command = new SQLiteCommand())
            {
                command.CommandText = "select * from [Decks] where [Name]=@Name";
                command.Parameters.AddWithValue("@Name", name);

                return GetDeck(command);
            }
        }

        public List<string> ListDecks()
        {
            using (SQLiteConnection connection = new SQLiteConnection(ConnectionString))
            {
                connection.Open();

                using (SQLiteCommand command = new SQLiteCommand(connection))
                {
                    command.CommandText = "SELECT [Name] from [Decks]";

                    List<string> decks = new List<string>();

                    using (SQLiteDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            decks.Add(ReadString(reader, "Name"));
                        }
                    }

                    return decks;
                }
            }
        }

        private Deck GetDeck(SQLiteCommand deckCommand)
        {
            //takes in the command used to select the deck to load. This is because there may be 
            //different where clauses, but only want to write one version of code to load the data
            using (SQLiteConnection connection = new SQLiteConnection(ConnectionString))
            {
                connection.Open();

                Deck deck = null;

                //Load the base deck information
                deckCommand.Connection = connection;
                using (SQLiteDataReader reader = deckCommand.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        deck = GetDeckHeader(reader);
                    }
                }

                //See if the deck exists
                if (deck == null) { return null; }

                //Now load the cards that make up the deck
                using (SQLiteCommand command = new SQLiteCommand(connection))
                {
                    command.CommandText = "SELECT * from [DeckCards] where [DeckID]=@DeckID";
                    command.Parameters.AddWithValue("@DeckID", deck.DeckID);

                    using (SQLiteDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            //read the card deck details
                            string multiverseID = ReadString(reader, "MultiverseID");
                            int quantity = ReadInt(reader, "Quantity");

                            //Retrieve the full card information
                            Card card = FindCard(multiverseID);

                            //Add the card and the quantity
                            deck.SetCardQuantity(card, quantity, false);
                        }                         
                    }
                }

                return deck;
            }
        }

        private Deck GetDeckHeader(SQLiteDataReader reader)
        {
            Deck deck = new Deck();

            deck.DeckID = ReadInt(reader, "DeckID");
            deck.Name = ReadString(reader, "Name");
            deck.Description = ReadString(reader, "Description");
            deck.IsConstructed = ReadBool(reader, "IsConstructed");

            return deck;
        }

        /// <summary>
        /// Used for importing or copying decks.
        /// </summary>
        /// <param name="deck"></param>
        /// <returns>The new deck</returns>
        public Deck ImportDeck(Deck deck, bool updateCardOwnedQuantities)
        {
            using (SQLiteConnection connection = new SQLiteConnection(ConnectionString))
            {
                connection.Open();

                using (SQLiteTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        //Create the base Deck with the Header Information from the existing deck
                        
                        //Because deck names must be unique we need to change the deck name if we are copying
                        //an existing deck. 
                        string newDeckName = String.Format("{0} - {1}", deck.Name, Path.GetRandomFileName());

                        //It's a new deck so we can keep the name passed in
                        if (deck.DeckID == 0)
                        {
                            newDeckName = deck.Name;
                        }

                        Deck newDeck = CreateDeck(newDeckName, deck.Description, connection, transaction);

                        //Now we need to add all the cards to the deck.
                        foreach (DeckCard card in deck.Cards)
                        {
                            int quantiyInDeck = card.Quantity;
                            AddCardToDeck(newDeck.DeckID, card.Card.MultiverseID, quantiyInDeck, connection, transaction);
                            newDeck.SetCardQuantity(card.Card, quantiyInDeck, false);

                            //If this new deck is comprised of all new cards (such as a recent purchase of a theme deck) we also
                            //need to update the quantity of owned cards.
                            if (updateCardOwnedQuantities)
                            {
                                UpdateCardQuantity(card.Card.MultiverseID, quantiyInDeck, true, connection, transaction);
                            }
                        }

                        transaction.Commit();

                        return newDeck;                        
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }
        #endregion

        private int ReadInt(SQLiteDataReader reader, string field)
        {
            if (reader[field] == null || reader[field] == DBNull.Value)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(reader[field]);
            }
        }

        private bool ReadBool(SQLiteDataReader reader, string field)
        {
            if (reader[field] == null || reader[field] == DBNull.Value)
            {
                return false;
            }
            else
            {
                return Convert.ToBoolean(reader[field]);
            }
        }

        private string ReadString(SQLiteDataReader reader, string field)
        {
            if (reader[field] == null || reader[field] == DBNull.Value)
            {
                return String.Empty;
            }
            else
            {
                return (string)reader[field];
            }
        }
    }
}
